using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Xml.Linq;
using System.Xml;
using System.Linq;
using System.Diagnostics;
using System.IO;
using System.Collections;


namespace GuavaData
{
    public interface Cell
    {
        bool hasChild();
        Cell getChild();
        DataSet Process(Transaction t);
        List<SemanticUnit> SemanticUnits(string table);
    }

    [global::System.Serializable]
    public class DDLSideEffectException : Exception
    {
        //
        // For guidelines regarding the creation of new exception types, see
        //    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconerrorraisinghandlingguidelines.asp
        // and
        //    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncscol/html/csharp07192001.asp
        //

        public DDLSideEffectException() { }
        public DDLSideEffectException(string message) : base(message) { }
        public DDLSideEffectException(string message, Exception inner) : base(message, inner) { }
        protected DDLSideEffectException(
          System.Runtime.Serialization.SerializationInfo info,
          System.Runtime.Serialization.StreamingContext context)
            : base(info, context) { }
    }

    public class Channel : Object
    {
        public Cell root;
        private SqlProvider sp;
        private Dictionary<string, Operator> qo;
        private Dictionary<string, Filter> q1;
        private Dictionary<string, int> ca;
        private List<Guid> qu;
        private Random r;
        StreamWriter file;
        
        public Channel()
        {
            sp = new SqlProvider();
            qo = new Dictionary<string, Operator>();
            q1 = new Dictionary<string, Filter>();
            ca = new Dictionary<string, int>();
            qu = new List<Guid>();
            r = new Random();
          //  file = new StreamWriter(new FileStream("output.csv", FileMode.OpenOrCreate));
            root = sp;
        }

        public void Add(Transform p)
        {
            if (p == null)
                throw new Exception("Trying to add a null transformation");
            p.Child = root;
            root = p;
        }

        public void AddToEnd(Transform p)
        {
            if (root is SqlProvider)
            {
                Add(p);
                return;
            }
            Cell temp = root;
            while (!((((Transform)temp).Child is SqlProvider)))
            {
                temp = ((Transform)temp).Child;
            }
            Cell provider = ((Transform)temp).Child;
            ((Transform)temp).Child = p;
            p.Child = provider;
        }

        public void RemoveLastTransform()
        {
            if (root is SqlProvider)
            {
                return;
            }
            Cell last = null;
            Cell temp = root;
            while (!((((Transform)temp).Child is SqlProvider)))
            {
                last = temp;
                temp = ((Transform)temp).Child;
            }
            if (last == null)
            {
                root = temp.getChild();
                return;
            }
            ((Transform)last).Child = temp.getChild();
        }

        public static Channel FromXDocument(XDocument xDoc)
        {
            Channel c = new Channel();
            if (xDoc.Root.HasElements == false)
            {
                return new Channel();
            }
            if (xDoc.Root.Elements().Count() == 0)
                throw new Exception("no elements");
            foreach (XElement trans in xDoc.Root.Elements().Reverse())
            {
                c.Add(Transform.FromXElement(trans));
            }
            return c;
   
        }

        public XDocument ToXDocument()
        {
            XDocument doc = new XDocument();
            doc.Add(new XElement("Channel"));
            if (this.root is Transform)
            {
                Transform t = (Transform)root;
                List<XElement> xEls = t.ToXElement();
                xEls.Reverse();
                doc.Element("Channel").Add(xEls);
            }
            return doc;

        }

        public static Channel InvertChannel(Channel c)
        {
            Channel newC = new Channel();
            if (c.root is Provider)
                return newC;
            Transform t = (Transform) c.root;
            Transform newT = t.Inverse();
            newC.Add(newT);
            while (!(t.Child is Provider))
            {
                t = (Transform)t.Child;
                Transform meh = t.Inverse();
                newC.Add(meh);
            }
            return newC;
        }

        public DataSet PoseRawQuery(string s)
        {
            DataSet ds = DBConnection.ExecuteQuery(s);
            return ds;
        }

        public DataSet PoseQueryCommand(Command cold)
        {

            Transaction t = new Transaction();
            /*
            //replace select with pre-transformed tablerefs
            if (cold.root is Filter)
            {
                Filter f1 = (Filter)cold.root;
                TableRef tr = (TableRef)f1.Child;
                string tabName = tr.Table.ToString();
               
                if (q1.ContainsKey(tabName))
                {
                    Filter f2 = (Filter)q1[tabName].Clone();
                    List<string> newcolumns = new List<string>();
                    for (int i = 0; i < f1.ColumnAliases.Count; i++)
                    {
                        string col = f1.ColumnAliases[i].Split('.')[1];
                        int alias = ca[tabName + "." + col];
                        newcolumns.Add("T_" + alias + "." + col);
                    }
                    f2.ColumnAliases = newcolumns;
                    f2.ColumnConditions = f1.ColumnConditions;
                    Command com = new Command();
                    com.root = f2;
                    t.Add(com);
                    return sp.Process(t);
                }
            }
            */
            t.Add(cold);

            return root.Process(t);
        }

        public DataSet PoseTransaction(Transaction t)
        {
            return root.Process(t);
        }

        public void PoseTransactionWithEmptyProvider(Transaction t)
        {
            
            if (root is SqlProvider)
                return;
            Cell temp = root;
            Cell last = null;
            while (temp.hasChild())
            {
                last = temp;
                temp = temp.getChild();
            }
            ((Transform)last).Child = new EmptyProvider();
            root.Process(t);
            ((Transform)last).Child = temp;
        }

        public String GetSQL(Transaction t)
        {
            Cell temp = root;
            Cell last = null;
            if (temp is SqlProvider)
            {
                root = new SqlTextProvider();
                root.Process(t);
                root = temp;
                return t.sqlTextResult;
            }
            while (temp.hasChild())
            {
                last = temp;
                temp = temp.getChild();
            }
            ((Transform)last).Child = new SqlTextProvider();
            root.Process(t);
            ((Transform)last).Child = temp;
            return t.sqlTextResult;
        }

        public List<SemanticUnit> SpikeForSemanticChanges(string table)
        {
            return root.SemanticUnits(table);
        }

        public void PreseedQueries(DataSet ds)
        {
            foreach(DataTable table in ds.Tables)
            {
                Command com = new Command();
                string tab = com.nextTableAlias(table.ToString());
                string tabName = table.ToString();
                TableRef tr = new TableRef(table.ToString(), tab);
                foreach (DataColumn dc in AppData.ds.Tables[tabName].Columns)
                    tr.AddColumn(dc.ColumnName, dc.ColumnName,
                        (GuavaData.Domain.Domain)dc.ExtendedProperties[Operator.DomainFlag]);

                Filter f = new Filter(tr, new String[] { tab + ".id" },
                    new Condition[] { Condition.NewCondition(ConditionType.EQ, "-__-") });
                com.root = f;
               
                this.PoseQueryCommand(com);


                qo.Add(tabName, f.Child);
                this.MapAliases(f, tabName);
                q1.Add(tabName, f);

            }
            
            return;
        }

        public void RunTestSuite(System.Windows.Forms.TextBox tbox)
        {
           // StreamWriter file = new StreamWriter(new FileStream("output.csv", FileMode.OpenOrCreate));
            //StreamWriter file2 = new StreamWriter(new FileStream("guids.txt", FileMode.OpenOrCreate));
            System.Diagnostics.Stopwatch swInsert = new System.Diagnostics.Stopwatch();
            System.Diagnostics.Stopwatch swQuery = new System.Diagnostics.Stopwatch();
            Guid gPatient = Guid.NewGuid();

            // Create a new patient
            RowConstant rc = new RowConstant();
            rc.AddValue("id", new GuavaData.Domain.UniqueIdentifier(), gPatient);
            rc.AddValue("firstname", new GuavaData.Domain.String(50), "Bob");
            rc.AddValue("lastname", new GuavaData.Domain.String(50), "Thomas");
            Insert ins = new Insert("Patient", new String[] { "id", "firstname", "lastname" }, rc);
            Transaction t = new Transaction(ins);
            this.PoseTransaction(t);

            sp.sw.Reset();
            sp.prov.Reset();
            bool first = true;
            Random r = new Random();
            List<Guid> qu = new List<Guid>();
            bool hema = false;
       
            //file.WriteLine("Insert - query test");
            for (int i = 0; i < 10000; i++)
            {
                qu = new List<Guid>();
                for (int j = 0; j < 100; j++)
                {
                    // Create the transaction
                    Transaction tInsert = new Transaction();

                    // Create procedure
                    Guid gProc = Guid.NewGuid();
                    
                    // Build list of "random" procedures to query for
                   // if (first)
                        qu.Add(gProc);
                   // else if (r.Next(0, 10) == 0)
                     //   qu[r.Next(0, 10)] = gProc;

                    RowConstant rcProc = new RowConstant();
                    rcProc.AddValue("id", new GuavaData.Domain.UniqueIdentifier(), gProc);
                    rcProc.AddValue("fk", new GuavaData.Domain.UniqueIdentifier(), gPatient);
                    rcProc.AddValue("Form", new GuavaData.Domain.String(11), "Colonoscopy");
                    rcProc.AddValue("Created", new GuavaData.Domain.DateTime(), DateTime.Now);
                    Insert insProc = new Insert("proc_main", new String[] { "id", "fk", "Form", "Created" }, rcProc);
                    tInsert.Add(new Command(insProc));

                 
                    RowConstant rcCol = new RowConstant();
                    List<string> cols = new List<string>();
                    rcCol.AddValue("id", new GuavaData.Domain.UniqueIdentifier(), gProc);
                    cols.Add("id");
                    rcCol.AddValue("Systolic", new GuavaData.Domain.TinyInteger(), Byte.Parse("120"));
                    cols.Add("Systolic");
                    rcCol.AddValue("tbPrepDose", new GuavaData.Domain.String(), "1.2");
                    cols.Add("tbPrepDose");
                    rcCol.AddValue("lmwh", new GuavaData.Domain.Enumerated(new String[] { "Yes", "No" }), "Yes");
                    cols.Add("lmwh");
                    rcCol.AddValue("Diastolic", new GuavaData.Domain.TinyInteger(), Byte.Parse("80"));
                    cols.Add("Diastolic");
                    rcCol.AddValue("Height", new GuavaData.Domain.TinyInteger(), Byte.Parse("77"));
                    cols.Add("Height");
                    rcCol.AddValue("Pulse", new GuavaData.Domain.SmallInteger(), Int16.Parse("70"));
                    cols.Add("Pulse");
                    rcCol.AddValue("aspirin", new GuavaData.Domain.String(), "sf");
                    cols.Add("aspirin");

                    //rcCol.AddValue("cfOtherAntibiotic", new GuavaData.Domain.TinyInteger(), Byte.Parse("1"));
                    //cols.Add("cfOtherAntibiotic");
                    rcCol.AddValue("cbASAClass", new GuavaData.Domain.String(), randomASAClass());
                    cols.Add("cbASAClass");
                    rcCol.AddValue("cbLevelOfSedation", new GuavaData.Domain.String(), randomLevelSed());
                    cols.Add("cbLevelOfSedation");
                    rcCol.AddValue("cbSedManagedBy", new GuavaData.Domain.String(), randomSedManaged());
                    cols.Add("cbSedManagedBy");
                    if (r.Next(3) == 2)
                    {
                        rcCol.AddValue("antiinflamCoag", new GuavaData.Domain.String(), "Yes");
                        cols.Add("antiinflamCoag");
                    }
                    if (r.Next(3) == 1)
                    {
                        rcCol.AddValue("coumadin", new GuavaData.Domain.String(), "No");
                        cols.Add("coumadin");
                    }
                    if (r.Next(3) == 2)
                    {
                        rcCol.AddValue("tbAntiCoagPlan", new GuavaData.Domain.String(), "other");
                        cols.Add("tbAntiCoagPlan");
                    }
                    if (r.Next(3) == 3)
                    {
                        rcCol.AddValue("cfNoSedMed", new GuavaData.Domain.TinyInteger(), Byte.Parse("1"));
                        cols.Add("cfNoSedMed");
                    }
                    if (r.Next(3) == 1)
                    {
                        rcCol.AddValue("cfResidualSedation", new GuavaData.Domain.TinyInteger(), Byte.Parse("1"));
                        cols.Add("cfResidualSedation");
                    }

                    if (r.Next(3) == 3)
                    {
                        rcCol.AddValue("cbProcQualBowelPrep", new GuavaData.Domain.String(), randomQualBowelPrep());
                        cols.Add("cbProcQualBowelPrep");
                    }

                   // if (r.Next(3) == 2)
                   // {
                       // rcCol.AddValue("cfHematochezia", new GuavaData.Domain.TinyInteger(), Byte.Parse("1"));
                       // cols.Add("cfHematochezia");
                       // hema = true;
                    //}

                    rcCol.AddValue("ProcedureDate", new GuavaData.Domain.DateTime(), randomDate());
                    cols.Add("ProcedureDate");

                    Insert insCol = new Insert("COL", cols.ToArray(), rcCol);
                    tInsert.Add(new Command(insCol));
                    
                    //if we checked cfHematochezia give a value to the type
                    if (hema){
                        RowConstant rcHema = new RowConstant();
                        rcHema.AddValue("raHematocheziaType", new GuavaData.Domain.String(), "other");
                        Insert insHema = new Insert("IndicationsHematochezia", new String[]{"raHematocheziaType"}, rcHema);
                        tInsert.Add(new Command(insHema));
                        hema = false;
                    }

                    // Create surgical history entry
                    RowConstant rcSurg = new RowConstant();
                    rcSurg.AddValue("id", new GuavaData.Domain.UniqueIdentifier(), gProc);
                    rcSurg.AddValue("bilBypass", new GuavaData.Domain.TinyInteger(0, 2), 1);
                    rcSurg.AddValue("cystRemoval", new GuavaData.Domain.TinyInteger(0, 2), 1);
                    Insert insSurg = new Insert("SurgicalHistory", new String[] { "id", "bilBypass", "cystRemoval" }, rcSurg);
                    tInsert.Add(new Command(insSurg));

                    // Create finding entry
                    Guid gFind = Guid.NewGuid();
                    RowConstant rcFind = new RowConstant();
                    rcFind.AddValue("id", new GuavaData.Domain.UniqueIdentifier(), gFind);
                    rcFind.AddValue("fk", new GuavaData.Domain.UniqueIdentifier(), gProc);
                    rcFind.AddValue("kind", new GuavaData.Domain.TinyInteger(), Byte.Parse("1"));
                    rcFind.AddValue("caption", new GuavaData.Domain.String(14), "Diverticulosis");
                    rcFind.AddValue("x", new GuavaData.Domain.SmallInteger(), Int16.Parse("106"));
                    rcFind.AddValue("y", new GuavaData.Domain.SmallInteger(), Int16.Parse("75"));
                    rcFind.AddValue("txtLeft", new GuavaData.Domain.SmallInteger(), Int16.Parse("126"));
                    rcFind.AddValue("txtTop", new GuavaData.Domain.SmallInteger(), Int16.Parse("63"));
                    
                    rcFind.AddValue("regions", new GuavaData.Domain.Integer(), Int32.Parse("16"));
                    rcFind.AddValue("diff", new GuavaData.Domain.TinyInteger(), Byte.Parse("0"));
                    rcFind.AddValue("procType", new GuavaData.Domain.String(5), "COL");
                    Insert insFind = new Insert("proc_find", new String[] {"id", "fk", "kind","caption", "x", "y",
                        "txtLeft", "txtTop",  "regions", "diff", "procType"}, rcFind);
                    tInsert.Add(new Command(insFind));

                    // Create diverticulosis entry
                    RowConstant rcDivert = new RowConstant();
                    rcDivert.AddValue("id", new GuavaData.Domain.UniqueIdentifier(), gFind);
                    rcDivert.AddValue("diverticulitis", new GuavaData.Domain.TinyInteger(), Byte.Parse("1"));
                    rcDivert.AddValue("bleedRate", new GuavaData.Domain.String(), "Inactive");
                    rcDivert.AddValue("cbStartLoc", new GuavaData.Domain.String(), "Transverse colon");
                    rcDivert.AddValue("cbEndLoc", new GuavaData.Domain.String(), "Transverse colon");
                    
                    rcDivert.AddValue("cbDivertDx", new GuavaData.Domain.String(), "Diverticulitis of colon");
                    Insert insDivert = new Insert("Diverticulosis", new String[] {"id", "diverticulitis",  "bleedRate","cbStartLoc", "cbEndLoc",
                        "cbDivertDx"}, rcDivert);
                    tInsert.Add(new Command(insDivert));

                    //create polyp entry
                    RowConstant rcPolyp = new RowConstant();
                    List<string> cols2 = new List<string>();
                    rcPolyp.AddValue("id", new GuavaData.Domain.UniqueIdentifier(), gFind);
                    cols2.Add("id");
                    if (r.Next(1, 3) == 1)
                    {
                        rcPolyp.AddValue("nbPolypSize", new GuavaData.Domain.TinyInteger(), Byte.Parse("1"));
                        cols2.Add("nbPolypSize");
                        rcPolyp.AddValue("cbPolypLocation", new GuavaData.Domain.String(), "y");
                        cols2.Add("cbPolypLocation");

                        rcPolyp.AddValue("raPolypType", new GuavaData.Domain.String(), "y");
                        cols2.Add("raPolypType");
                    }
                    tInsert.Add(new Command(new Insert("Polyp", cols2.ToArray(), rcPolyp)));

                    // Execute the transaction
                    swInsert.Start();
                    this.PoseTransaction(tInsert);
                    swInsert.Stop();
                }

                //string insTime, insProv, insDB, queryTime, queryProv, queryDB;
                /*
                insTime = swInsert.ElapsedMilliseconds.ToString();
                insDB = sp.sw.ElapsedMilliseconds.ToString();
                insProv = sp.prov.ElapsedMilliseconds.ToString();
                sp.sw.Reset();
                sp.prov.Reset();
                swInsert.Reset();

                
                foreach (Guid g in qu)
                {
                    // Execute the "get me this colonoscopy" query
                    Command com = new Command();
                    string tab = com.nextTableAlias("COL");
                    TableRef tr = new TableRef("COL", tab);

                    foreach (DataColumn dc in AppData.ds.Tables["COL"].Columns)
                        tr.AddColumn(dc.ColumnName, dc.ColumnName,
                            (GuavaData.Domain.Domain)dc.ExtendedProperties[Operator.DomainFlag]);

                    Filter f = new Filter(tr, new String[] { tab + ".id" },
                        new Condition[] { Condition.NewCondition(ConditionType.EQ, g) });
                    com.root = f;

                    swQuery.Start();
                    this.PoseQueryCommand(com);
                    swQuery.Stop();
                }

                queryTime = swQuery.ElapsedMilliseconds.ToString();
                queryDB = sp.sw.ElapsedMilliseconds.ToString();
                queryProv = sp.prov.ElapsedMilliseconds.ToString();

                swQuery.Reset();
                sp.sw.Reset();
                sp.prov.Reset();

                first = false;

                file.WriteLine(String.Join(",",
                    new String[] { insTime, insDB, insProv, queryTime, queryDB, queryProv}));
                 */
                if (i % 10 == 0)
                {
                    tbox.Text = i.ToString();
                    tbox.Refresh();
                }
                
                
            }

            //MoreTests();
            tbox.Text = "done";
            tbox.Refresh();
           // file.Close();
        }
        public void MoreTests()
        {
            int tests = 1;
            //runQM(tests);
            //runquery(tests);
            runAddDropColumnTest(tests);
            //runUpdateTest(tests);
            //runDeleteTest(tests);
            file.Close();
            return;

        }

        private void runquery(int numTests)
        {
            file.WriteLine("q test");
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            string queryTime, queryProv, queryDB;

            for (int i = 0; i < numTests; i++)
            {
                sw.Reset();
                sp.sw.Reset();
                sp.prov.Reset();
                for (int j = 0; j < 10; j++)
                {
                    Command com = new Command();
                    string tab = com.nextTableAlias("COL");
                    TableRef tr = new TableRef("COL", tab);

                    foreach (DataColumn dc in AppData.ds.Tables["COL"].Columns)
                        tr.AddColumn(dc.ColumnName, dc.ColumnName,
                            (GuavaData.Domain.Domain)dc.ExtendedProperties[Operator.DomainFlag]);

                    Filter f = new Filter(tr, new String[] { tab + ".ProcedureDate" },
                        new Condition[] { Condition.NewCondition(ConditionType.LE, DateTime.Now) });
                    com.root = f;

                    sw.Start();
                    this.PoseQueryCommand(com);
                    sw.Stop();
                }

                queryTime = sw.ElapsedMilliseconds.ToString();
                queryDB = sp.sw.ElapsedMilliseconds.ToString();
                queryProv = sp.prov.ElapsedMilliseconds.ToString();
                file.WriteLine(String.Join(",",
                    new String[] { queryTime, queryDB, queryProv }));
            }
            return;
        }

        private void runQM(int numTests)
        { 
            file.WriteLine("qm test");
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            string queryTime, queryProv, queryDB;

                //qm
                Transaction t = new Transaction();
                Command com = new Command();
                string tab = com.nextTableAlias("COL");
                TableRef tr = new TableRef("COL", tab);

                foreach (DataColumn dc in AppData.ds.Tables["COL"].Columns)
                    tr.AddColumn(dc.ColumnName, dc.ColumnName,
                        (GuavaData.Domain.Domain)dc.ExtendedProperties[Operator.DomainFlag]);

                Filter f = new Filter(tr, new String[] { tab + ".ProcedureDate" },
                    new Condition[] { Condition.NewCondition(ConditionType.EQ, DateTime.Now.AddDays(-1)) });
                Filter f2 = new Filter(f);
                f2.AddCondition("cbASAClass", Condition.NewCondition(ConditionType.ISNOTNULL));
                t.Add(new Command(f2));
                Filter f3 = new Filter(f);
                f3.AddCondition("antiinflamCoag", Condition.NewCondition(ConditionType.ISNOTNULL));
                Filter f4 = new Filter(f3);
                f4.AddCondition("coumadin", Condition.NewCondition(ConditionType.ISNOTNULL));
                Filter f5 = new Filter(f4);
                f5.AddCondition("tbAntiCoagPlan", Condition.NewCondition(ConditionType.ISNOTNULL));
                t.Add(new Command(f5));
                Filter f6 = new Filter(f);
                f6.AddCondition("cfNoSedMed", Condition.NewCondition(ConditionType.ISNOTNULL));
                f6.AddCondition("cfResidualSedation", Condition.NewCondition(ConditionType.ISNOTNULL));
                f6.AddCondition("cbLevelOfSedation", Condition.NewCondition(ConditionType.EQ, "General anesthesia"));
                f6.AddCondition("cbSedManagedBy", Condition.NewCondition(ConditionType.EQ, "Anesthesiologist"));
                f6.AddCondition("cbSedManagedBy", Condition.NewCondition(ConditionType.EQ, "Nurse anesthetist"));
                t.Add(new Command(f6));
                Filter f7 = new Filter(f);
                f7.AddCondition("cbProcQualBowelPrep", Condition.NewCondition(ConditionType.EQ, "Excellent"));
                f7.AddCondition("cbProcQualBowelPrep", Condition.NewCondition(ConditionType.EQ, "Fair"));
                f7.AddCondition("cbProcQualBowelPrep", Condition.NewCondition(ConditionType.EQ, "Good"));
                t.Add(new Command(f7));
                Filter f8 = new Filter(f);
                f8.AddCondition("cfHematochezia", Condition.NewCondition(ConditionType.ISNOTNULL));
                string tab2 = com.nextTableAlias("IndicationsHematochezia");
                TableRef tr2 = new TableRef("IndicationsHematochezia", tab);

                foreach (DataColumn dc in AppData.ds.Tables["IndicationsHematochezia"].Columns)
                    tr2.AddColumn(dc.ColumnName, dc.ColumnName,
                        (GuavaData.Domain.Domain)dc.ExtendedProperties[Operator.DomainFlag]);
                Filter f9 = new Filter(tr2, new String[] { tab + ".raHematocheziaType" },
                    new Condition[] { Condition.NewCondition(ConditionType.ISNOTNULL)});
                //t.Add(new Command(new Join(f9,f8,new string [] {"id"}, new Condition[] {Condition.NewCondition(ConditionType.EQ, "id")}, GuavaData.JoinType.INNER)));
                /*string tab3 = com.nextTableAlias("Polyp");
                TableRef tr3 = new TableRef("Polyps", tab);

                foreach (DataColumn dc in AppData.ds.Tables["Polyp"].Columns)
                    tr3.AddColumn(dc.ColumnName, dc.ColumnName,
                        (GuavaData.Domain.Domain)dc.ExtendedProperties[Operator.DomainFlag]);
                Filter f10 = new Filter(tr3, new String[] { tab + ".cbPolypLocation" },
                    new Condition[] { Condition.NewCondition(ConditionType.ISNOTNULL)});
                f10.AddCondition("nbPolypSize", Condition.NewCondition(ConditionType.ISNOTNULL));
                f10.AddCondition("raPolypType", Condition.NewCondition(ConditionType.ISNOTNULL));
                Join j = new Join(f10,f, new string [] {"id"}, new Condition[] {Condition.NewCondition(ConditionType.EQ, "id")}, GuavaData.JoinType.INNER)));
            */
                for (int i = 0; i < numTests; i++)
                {
                    for (int j=0; j<10; j++)
                    {
                        sw.Start();
                        this.PoseTransaction(t);
                        sw.Stop();
                    }
                    queryTime = sw.ElapsedMilliseconds.ToString();
                    queryDB = sp.sw.ElapsedMilliseconds.ToString();
                    queryProv = sp.prov.ElapsedMilliseconds.ToString();

                    sw.Reset();
                    sp.sw.Reset();
                    sp.prov.Reset();

                    file.WriteLine(String.Join(",",
                    new String[] {queryTime, queryDB, queryProv}));
                }

            
            return;
        }

        private void runAddDropColumnTest(int numTests)
        {
            file.WriteLine("add column test");
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            System.Diagnostics.Stopwatch dw = new System.Diagnostics.Stopwatch();
            string queryTime, queryProv, queryDB, dropTime, dropProv, dropDB;

            for (int i = 0; i < numTests; i++)
            {
                sw.Reset();
                dw.Reset();
                sp.sw.Reset();
                sp.prov.Reset();
                for (int j = 0; j < 100; j++)
                {
                    Transaction t = new Transaction();
                    AddColumn ac = new AddColumn("COL", "data_test" + j.ToString(), new GuavaData.Domain.TinyInteger());
                    t.Add(new Command(ac));
                   // AddColumn ac2 = new AddColumn("COL", "strn_test" + j.ToString(), new GuavaData.Domain.String());
                    //t.Add(new Command(ac2));

                    sw.Start();
                    this.PoseTransaction(t);
                    sw.Stop();

                }
                queryTime = sw.ElapsedMilliseconds.ToString();
                queryDB = sp.sw.ElapsedMilliseconds.ToString();
                queryProv = sp.prov.ElapsedMilliseconds.ToString();

                List<Guid> gl = getGuids(100);
                foreach (Guid g in gl)
                {
                        RowConstant rc = new RowConstant();
                        List<string> cols = new List<string>();
                        for (int k = 0; k < 10; k++)
                        {
                            rc.AddValue("data_test" + k.ToString(), new GuavaData.Domain.TinyInteger(), Byte.Parse("1"));
                            cols.Add("data_test" + k.ToString());
                        }
                       // rc.AddValue("strn_test2", new GuavaData.Domain.String(), "test");
                        Update u = new Update("COL", cols.ToArray(), rc);
                        u.AddCondition("id", Condition.NewCondition(ConditionType.EQ, g));
                        this.PoseQueryCommand(new Command(u));

                 }

                 sp.sw.Reset();
                 sp.prov.Reset();
                 for (int j = 0; j < 100; j++)
                 {
                    Transaction t2 = new Transaction();
                    DropColumn dc = new DropColumn("COL", "data_test"+j.ToString());
                    t2.Add(new Command(dc));
                   // DropColumn dc2 = new DropColumn("COL", "strn_test"+j.ToString());
                    //t2.Add(new Command(dc2));

                    dw.Start();
                    this.PoseTransaction(t2);
                    dw.Stop();
                }
                
                dropTime = dw.ElapsedMilliseconds.ToString();
                dropDB = sp.sw.ElapsedMilliseconds.ToString();
                dropProv = sp.prov.ElapsedMilliseconds.ToString();

                file.WriteLine(String.Join(",",
                    new String[] { queryTime, queryDB, queryProv, dropTime, dropDB, dropProv }));
            }

            
            return; 
        }

        private void runUpdateTest(int numTests)
        {
           // StreamWriter file = new StreamWriter(new FileStream("output.csv", FileMode.OpenOrCreate));
            file.WriteLine("update test");
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            
            for (int i = 0; i < numTests; i++)
            {
                List<Guid> gl = getGuids(10);
                sw.Reset();
                sp.sw.Reset();
                sp.prov.Reset();

                foreach (Guid g in gl)
                {
                    RowConstant rc = new RowConstant();
                    rc.AddValue("Height", new GuavaData.Domain.TinyInteger(), Byte.Parse("60"));
                    rc.AddValue("Pulse", new GuavaData.Domain.SmallInteger(), Int16.Parse("45"));
                    rc.AddValue("aspirin", new GuavaData.Domain.String(), "sf");
                    rc.AddValue("cbSedManagedBy", new GuavaData.Domain.String(), randomSedManaged());
                    Update u = new Update("COL", new string[] { "Height", "Pulse", "aspirin", "cbSedManagedBy" }, rc);
                    u.AddCondition("id", Condition.NewCondition(ConditionType.EQ, g));

                    sw.Start();
                    this.PoseQueryCommand(new Command(u));
                    sw.Stop();

                }
                string queryTime, queryProv, queryDB;
                queryTime = sw.ElapsedMilliseconds.ToString();
                queryDB = sp.sw.ElapsedMilliseconds.ToString();
                queryProv = sp.prov.ElapsedMilliseconds.ToString();

                file.WriteLine(String.Join(",",
                    new String[] {queryTime, queryDB, queryProv }));
            }

           // file.Close();
            return; 
        }

        private void runDeleteTest(int numTests) 
        { 
            StreamWriter file = new StreamWriter(new FileStream("delete.csv", FileMode.OpenOrCreate));
            file.WriteLine("delete test");
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            
            for (int i = 0; i < numTests; i++)
            {
                List<Guid> gl = getGuids(10);
                sw.Reset();
                sp.sw.Reset();
                sp.prov.Reset();

                foreach (Guid g in gl)
                {
                    Delete d = new Delete("COL");
                    d.AddCondition("id", Condition.NewCondition(ConditionType.EQ, g));
                    sw.Start();
                    this.PoseQueryCommand(new Command(d));
                    sw.Stop();
                }
                string queryTime, queryProv, queryDB;
                queryTime = sw.ElapsedMilliseconds.ToString();
                queryDB = sp.sw.ElapsedMilliseconds.ToString();
                queryProv = sp.prov.ElapsedMilliseconds.ToString();

                file.WriteLine(String.Join(",",
                    new String[] { queryTime, queryDB, queryProv }));

            }

            file.Close();
            return; 
        }

        private List<Guid> getGuids(int num)
        {
            List<Guid> gl = new List<Guid>();
            DataSet dset = this.PoseRawQuery("SELECT TOP " + num + " id FROM proc_main ORDER BY NEWID()");
            if (dset.Tables.Count != 1) throw new Exception("getGuid did not pull one table");
            foreach( DataRow dr in dset.Tables[0].Rows)
                gl.Add((new Guid(dr["id"].ToString())));
            return gl;
        }

        private string randomASAClass()
        {
            switch (r.Next(1, 6))
            {
                case 1: return "I   - Normal healthy patient";
                case 2: return "II  - Mild systemic disease";
                case 3: return "III - Severe systemic disease";
                case 4: return "IV  - Severe systemic disease; life-threatening";
                case 5: return "V   - Moribund; not expected to survive";
                default: return "";
            }
        }

        private string randomLevelSed()
        {
            if (r.Next(1, 2) == 1)
                return "General anesthesia";
            return "";
        }

        private string randomSedManaged()
        {
            switch (r.Next(1, 3))
            {
                case 1: return "Anesthesiologist";
                case 2: return "Nurse anesthetist";
                default: return "Other";
            }
        }

        private string randomQualBowelPrep()
        {
            switch (r.Next(1, 4))
            {
                case 1: return "Excellent";
                case 2: return "Good";
                case 3: return "Fair";
                default: return "Poor";
            }
        }

        private DateTime randomDate()
        {
            switch (r.Next(1, 5))
            {
                case 1: return DateTime.Now;
                case 2: return (DateTime.Now.AddDays(-10));
                case 3: return (DateTime.Now.AddDays(10));
                case 4: return (DateTime.Now.AddDays(-20));
                case 5: return (DateTime.Now.AddDays(20));
                default: return DateTime.Now;
            }
        }
        public void MapAliases(AntiSemiJoin asj, string table)
        {
            asj.Child1.Visit(this, table);
            asj.Child2.Visit(this, table);
            return;
        }

        public void MapAliases(ApplyOp ap, string table)
        {
            ap.Child.Visit(this, table);
            return;
        }

        public void MapAliases(CrossJoin cj, string table)
        {
            cj.Child1.Visit(this, table);
            cj.Child2.Visit(this, table);

            return;
        }

        public void MapAliases(Except e, string table)
        {
            e.Child1.Visit(this, table);
            e.Child2.Visit(this, table);
            return;
        }

        public void MapAliases(Filter f, string table)
        {
            f.Child.Visit(this, table);
            return;
        }

        public void MapAliases(Insert i, string table)
        {
            i.Child.Visit(this, table);
            return;
        }

        public void MapAliases(Intersect i, string table)
        {
            for (int j = 0; j < i.Children.Count; j++)
                i.Children[j].Visit(this,table);
            return;
        }

        public void MapAliases(Join j, string table)
        {
            j.Child1.Visit(this, table);
            j.Child2.Visit(this, table);
            return;
        }

        public void MapAliases(Operator o, string table)
        {
            return;
        }
        public void MapAliases(PivotOp po, string table)
        {
            int alias = Convert.ToInt32(po.NewAlias.Split('_')[1]);
            foreach (string col in po.exposedColumns)
            {
                if (!ca.ContainsKey(table + "." + col))
                    ca.Add(table + "." + col, alias);
            }
            return;
        }

        public void MapAliases(Project p, string table)
        {
            p.Child.Visit(this, table);
            return;
        }

        public void MapAliases(TableRef tr, string table)
        {
            int alias = Convert.ToInt32(tr.Alias.Split('_')[1]);
            foreach (string col in tr.Columns)
            {
                if (!ca.ContainsKey(table + "." + col))
                    ca.Add(table + "." + col, alias);
            }
            return;
        }

        public void MapAliases(Union u, string table)
        {
            for (int i = 0; i < u.Children.Count; i++)
                u.Children[i].Visit(this, table);
            return;
        }

        public void MapAliases(UnpivotOp uo, string table)
        {
            uo.Child.Visit(this, table);
            return;
        }

        public void MapAliases(Update up, string table)
        {
            up.Child.Visit(this, table);
            return;
        }

    }
}
